-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 08, 2025 at 03:36 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ldp_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `enrollment`
--

CREATE TABLE `enrollment` (
  `id` int(11) NOT NULL,
  `training_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('enrolled','in-progress','completed') NOT NULL DEFAULT 'enrolled',
  `completion_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `enrollment`
--

INSERT INTO `enrollment` (`id`, `training_id`, `user_id`, `status`, `completion_date`, `created_at`) VALUES
(5, 3, 8, 'enrolled', NULL, '2025-11-16 10:08:50'),
(6, 3, 6, 'enrolled', NULL, '2025-11-16 10:10:03'),
(7, 4, 8, 'enrolled', NULL, '2025-11-16 11:15:09'),
(8, 6, 6, 'enrolled', NULL, '2025-12-08 02:01:59'),
(9, 7, 8, 'enrolled', NULL, '2025-12-08 02:05:31');

-- --------------------------------------------------------

--
-- Table structure for table `enrollments`
--

CREATE TABLE `enrollments` (
  `id` int(11) NOT NULL,
  `training_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `status` enum('enrolled','in-progress','completed') NOT NULL DEFAULT 'enrolled',
  `completion_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `materials`
--

CREATE TABLE `materials` (
  `id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `type` enum('pdf','video') NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `video_url` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `materials`
--

INSERT INTO `materials` (`id`, `training_id`, `title`, `type`, `file_path`, `video_url`, `uploaded_by`, `created_at`) VALUES
(4, 3, 'Paper', 'pdf', '1761776239_Alahid_Angeline.pdf', NULL, 1, '2025-10-29 22:17:19');

-- --------------------------------------------------------

--
-- Table structure for table `personnel`
--

CREATE TABLE `personnel` (
  `id` int(11) NOT NULL,
  `employee_id` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `position` varchar(150) NOT NULL,
  `employee_type` enum('Faculty','Non-Teaching','Trainer','Faculty') DEFAULT 'Faculty',
  `status` enum('Permanent','Contract of Services (COS)','Job Order (JO)') DEFAULT 'Permanent',
  `address` varchar(255) DEFAULT NULL,
  `cpnumber` varchar(255) DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `level` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `gender` varchar(40) NOT NULL,
  `sstatus` varchar(200) NOT NULL,
  `department` varchar(100) NOT NULL,
  `traineetype` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `personnel`
--

INSERT INTO `personnel` (`id`, `employee_id`, `name`, `position`, `employee_type`, `status`, `address`, `cpnumber`, `type`, `level`, `created_at`, `updated_at`, `date_from`, `date_to`, `gender`, `sstatus`, `department`, `traineetype`) VALUES
(6, '56768', 'Joseph Manlapaz', 'Instructor 1', 'Faculty', 'Job Order (JO)', '', '', NULL, '', '2025-11-16 07:51:55', '2025-11-16 10:34:58', NULL, NULL, '', '', 'CAS', ''),
(7, '56768', 'James Malto', 'Instructor III', 'Trainer', 'Contract of Services (COS)', '', '', NULL, '', '2025-11-16 08:36:46', '2025-11-16 10:34:53', NULL, NULL, '', '', 'CAS', ''),
(8, '56769', 'Junie Kelvin', 'Instructor II', 'Faculty', 'Permanent', NULL, NULL, NULL, NULL, '2025-11-16 10:01:17', '2025-11-16 10:26:05', NULL, NULL, '', '', 'CAS', '');

-- --------------------------------------------------------

--
-- Table structure for table `training`
--

CREATE TABLE `training` (
  `id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `traineename` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `schedule` date DEFAULT NULL,
  `trainer_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `leveloftraining` varchar(100) NOT NULL,
  `lnd` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `training`
--

INSERT INTO `training` (`id`, `title`, `traineename`, `description`, `date_from`, `date_to`, `schedule`, `trainer_id`, `created_at`, `updated_at`, `leveloftraining`, `lnd`) VALUES
(3, 'CITY MARKET TRAINING', '8', 'grade1', '2025-11-17', '2025-11-20', NULL, 7, '2025-11-16 08:42:01', '2025-12-08 02:24:07', 'Local', 'Supervisory'),
(4, 'CABITAN CHILDREN TRAINING', '7', 'jgjsdjg', '2025-11-18', '2025-11-19', NULL, NULL, '2025-11-16 11:14:58', '2025-12-08 02:23:55', 'National', 'Supervisory'),
(6, 'DAR TRAINING', '6', 'dsgfwrgrw', '2025-11-29', '2025-12-01', NULL, NULL, '2025-11-16 12:01:35', '2025-12-08 02:23:37', 'Regional', 'Supervisory'),
(7, 'GAD TRAINING', '6', 'TRAINING FOR FEMALE MAINTENANCE AND ASSETS', '2025-12-08', '2025-12-09', NULL, NULL, '2025-12-08 02:04:07', '2025-12-08 02:09:12', 'Local', 'Technical');

-- --------------------------------------------------------

--
-- Table structure for table `trainings`
--

CREATE TABLE `trainings` (
  `id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `traineename` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `schedule` date DEFAULT NULL,
  `trainer_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `leveloftraining` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `trainings`
--

INSERT INTO `trainings` (`id`, `title`, `traineename`, `description`, `date_from`, `date_to`, `schedule`, `trainer_id`, `created_at`, `updated_at`, `leveloftraining`) VALUES
(3, 'CABITAN COMPUTER LETERACY', 'Jomz', 'lkjdvkdv', '2025-10-29', '2025-10-30', NULL, 1, '2025-10-29 22:10:38', '2025-11-16 08:18:21', 'National');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','hr','trainer','employee') NOT NULL DEFAULT 'employee',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `password`, `role`, `status`, `created_at`, `updated_at`) VALUES
(1, 'System Admin', 'admin@gmail.com', '$2y$10$BDyQY.AtSJCAwKMhktdTGeW42R6JTwCbTkGC0/KqzyljShDmBkIty', 'admin', 'active', '2025-08-24 10:53:00', '2025-11-16 07:04:41');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `enrollment`
--
ALTER TABLE `enrollment`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `enrollments`
--
ALTER TABLE `enrollments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_training_user` (`training_id`,`user_id`),
  ADD KEY `fk_enr_user` (`user_id`);

--
-- Indexes for table `materials`
--
ALTER TABLE `materials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `training_id` (`training_id`),
  ADD KEY `uploaded_by` (`uploaded_by`);

--
-- Indexes for table `personnel`
--
ALTER TABLE `personnel`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `training`
--
ALTER TABLE `training`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `trainings`
--
ALTER TABLE `trainings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_trainer` (`trainer_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `enrollment`
--
ALTER TABLE `enrollment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `enrollments`
--
ALTER TABLE `enrollments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `materials`
--
ALTER TABLE `materials`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `personnel`
--
ALTER TABLE `personnel`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `training`
--
ALTER TABLE `training`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `trainings`
--
ALTER TABLE `trainings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `enrollments`
--
ALTER TABLE `enrollments`
  ADD CONSTRAINT `fk_enr_training` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_enr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `materials`
--
ALTER TABLE `materials`
  ADD CONSTRAINT `materials_ibfk_1` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`),
  ADD CONSTRAINT `materials_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `trainings`
--
ALTER TABLE `trainings`
  ADD CONSTRAINT `fk_trainer` FOREIGN KEY (`trainer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
